// ================================================================================
// 		File: PartyBranchActivitie.cs
// 		Desc: 
//  
// 		Called by:   
//               
// 		Auth: chenwenzhi（）
// 		Date: 2016-03-23 01:45
// ================================================================================
// 		Change History
// ================================================================================
// 		Date:		Author:				Description:
// 		--------	--------			-------------------
//    
// ================================================================================
// 
// ================================================================================
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace ZHDJ.Data.Models.Center
{
    /// <summary>
    /// 支部活动
    /// </summary>   
    public class PartyBranchActivitie : EntityBase
    {
        #region 属性
        /// <summary>
        /// 组织ID
        /// </summary>
        public string BranchID { get; set; }

        /// <summary>
        /// 活动标题
        /// </summary>
        public string Title { get; set; }

        /// <summary>
        /// 所属专题
        /// </summary>
        public string Thematic { get; set; }

        /// <summary>
        /// 活动简介
        /// </summary>
        public string Summary { get; set; }

        /// <summary>
        /// 活动开始时间
        /// </summary>
        public DateTime? BeginTime { get; set; }

        /// <summary>
        /// 活动结束时间
        /// </summary>
        public DateTime? EndTime { get; set; }

        /// <summary>
        /// 活动地点
        /// </summary>
        public string Address { get; set; }

        /// <summary>
        /// 地图坐标
        /// </summary>
        public string Coordinate { get; set; }

        /// <summary>
        /// 短信通知
        /// </summary>
        public string IsSmsAlert { get; set; }

        /// <summary>
        /// 短信通知内容
        /// </summary>
        public string SmsContent { get; set; }

        /// <summary>
        /// 图片ID
        /// </summary>
        public string ImageID { get; set; }

        /// <summary>
        /// 是否结束
        /// </summary>
        public bool? IsEnd { get; set; }

        /// <summary>
        /// 活动记录
        /// </summary>
        public string Record { get; set; }

        /// <summary>
        /// 0、无；1、置顶；2、固顶；3、置顶+固顶；
        /// </summary>
        public int Flags { get; set; }

        /// <summary>
        /// 发布状态( 1 草稿 2创建 4发布 )
        /// </summary>
        public int Status { get; set; }

        /// <summary>
        /// 审核状态(1审核中 2驳回  4审核通过)
        /// </summary>
        public int Aduit { get; set; }

        /// <summary>
        /// 是否已推送到上级
        /// </summary>
        public string IsPushUp { get; set; }

        /// <summary>
        /// 源ID
        /// </summary>
        public string RelationID { get; set; }

        /// <summary>
        /// 创建用户
        /// </summary>
        public string CreateBy { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; }

        /// <summary>
        /// 更新时间
        /// </summary>
        public DateTime? ModifyTime { get; set; }

        /// <summary>
        /// 修改用户
        /// </summary>
        public string ModifyBy { get; set; }

        #endregion

        #region 查询管理

        public static DataSet GetGridData(int pageIndex, int pageSize, string title, string branchId, string isEnd, string flags)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  a.ID,a.Title,BeginTime,EndTime,if(IsEnd=1,'是','否') as IsEndName,UserName,
                        if(Status=4,'是','否') as StatusName,if(Flags=1,'是','否') as FlagsName
                        from tblpartybranchactivitie a
                        left join tblpartyorgmember m on a.CreateBy=m.LoginID
                        where 1=1 ");

            if (!string.IsNullOrEmpty(title))
            {
                sb.Append(" and a.Title like ?Title ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "Title", Value = string.Format("%{0}%", title) });
            }
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and a.BranchID=?BranchID ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = string.Format("{0}", branchId) });
            }
            if (!string.IsNullOrEmpty(isEnd))
            {
                sb.Append(" and IsEnd=?IsEnd ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "IsEnd", Value = string.Format("{0}", isEnd) });
            }
            if (!string.IsNullOrEmpty(flags))
            {
                sb.Append(" and a.Flags=?Flags ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "Flags", Value = string.Format("{0}", flags) });
            }

            sb.Append(" order by a.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        /// <summary>
        /// 我的支部活动
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="title"></param>
        /// <param name="isEnd"></param>
        /// <param name="isMine"></param>
        /// <param name="loginId"></param>
        /// <returns></returns>
        public static DataSet GetMyBranchActivity(int pageIndex, int pageSize, string title, string isEnd, string isMine, string loginId, string branchId = "")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  a.ID,Title,BeginTime,EndTime,if(IsEnd=1,'是','否') as IsEndName,BranchName,
                        if(Flags=1,'是','否') as FlagsName,DDicMemberName as ThematicName
                        from tblpartybranchactivitie a
                        left join tblpartyorgbranch b on a.BranchID=b.BranchID
                        left join tblpartybranchactivitiemember m on m.ActivitieID=a.ID
                        left join tbldatadictionary d on DDicType='ZHDJ.BranchActivity.Thematic' and DDicMember=Thematic
                        where 1=1 ");

            if (!string.IsNullOrEmpty(loginId))
            {
                sb.Append(" and LoginID=?LoginID ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "LoginID", Value = string.Format("{0}", loginId) });
            }
            if (!string.IsNullOrEmpty(title))
            {
                sb.Append(" and Title like ?Title ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "Title", Value = string.Format("%{0}%", title) });
            }
            if (!string.IsNullOrEmpty(isEnd))
            {
                sb.Append(" and IsEnd=?IsEnd ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "IsEnd", Value = string.Format("{0}", isEnd) });
            }
            if (!string.IsNullOrEmpty(isMine))
            {
                sb.AppendFormat(" and (m.LoginID='{0}' and m.Status=5)", loginId);
            }
            else
            {
                sb.AppendFormat(" and m.LoginID='{0}'", loginId);
            }
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and a.BranchID in (select childbranchid from tblpartyorgbranchrelation a where a.BranchID=?BranchID)");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = branchId });
            }
            sb.Append(" order by a.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        public static DataSet GetBranchActivitieTitle(string branchId, string countFilter = "")
        {
            StringBuilder sb = new StringBuilder();
            List<MySqlParameter> list = new List<MySqlParameter>();
            sb.Append("select * from (select id,title,imageid,CreateTime,BranchID,Coordinate Module from tblpartybranchactivitie where IsEnd=1");
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and (BranchID in (select childbranchid from tblpartyorgbranchrelation a where a.BranchID=?BranchID) or BranchID=?BranchID) union all select id,title,imageid,CreateTime,BranchID,Module Module from tblpartyarticle where Module = 'Organizational' and Flags = 1 and  BranchID in (select childbranchid from tblpartyorgbranchrelation b where b.BranchID=?BranchID)");
                list.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = branchId });
            }
            sb.AppendFormat(") t order by CreateTime desc {0};SELECT FOUND_ROWS();", countFilter);

            return MySqlHelper.ExecuteDataSet(sb.ToString(), list.ToArray());
        }

        public static DataSet GetBranchActivitieTitleNoFlags(string branchId, string countFilter = "")
        {
            StringBuilder sb = new StringBuilder();
            List<MySqlParameter> list = new List<MySqlParameter>();
            sb.Append("select * from (select id,title,imageid,CreateTime,BranchID,Coordinate Module from tblpartybranchactivitie where IsEnd=1");
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and (BranchID in (select childbranchid from tblpartyorgbranchrelation a where a.BranchID=?BranchID) or BranchID=?BranchID) union all select id,title,imageid,CreateTime,BranchID,Module Module from tblpartyarticle where Module = 'Organizational' and  BranchID in (select childbranchid from tblpartyorgbranchrelation b where b.BranchID=?BranchID)");
                list.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = branchId });
            }
            sb.AppendFormat(") t order by CreateTime desc {0};SELECT FOUND_ROWS();", countFilter);

            return MySqlHelper.ExecuteDataSet(sb.ToString(), list.ToArray());
        }

        public static DataSet GetMoreActivitieTitle(string branchId, int pageSize, int startIndex)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            StringBuilder sb = new StringBuilder();

            sb.Append("select SQL_CALC_FOUND_ROWS ID,Title,CreateTime from tblpartybranchactivitie where IsEnd=1");
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and BranchID in (select childbranchid from tblpartyorgbranchrelation a where a.BranchID=?BranchID)");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = branchId });
            }
            sb.AppendFormat(" order by Flags desc,CreateTime desc limit {0},{1};SELECT FOUND_ROWS();", startIndex, pageSize);
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        public static string QueryActivityMembers(string id)
        {
            StringBuilder sbMembers = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>() { };

            sbMembers.Append(@"SELECT UserName FROM tblpartybranchactivitiemember a
LEFT JOIN tblpartyorgmember b on a.LoginID=b.LoginID
WHERE ActivitieID=?ActivitieID");
            parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "ActivitieID", Value = id });

            DataTable dt = MySqlHelper.ExecuteDataTable(sbMembers.ToString(), parameters.ToArray());

            foreach (DataRow dr in dt.Rows)
            {
                if (sbMembers.Length > 0)
                    sbMembers.AppendFormat(",{0}", dr["UserName"]);
                else
                    sbMembers.Append(dr["UserName"]);
            }

            return sbMembers.ToString();
        }
        #endregion
    }
}
